﻿/**             © 2008 Avanade Inc. All Rights Reserved.
 * 
 * Authors:     Joris Valkonet joris.valkonet@avanade.com  Avanade Netherlands
 *              Thanh Luc      thanh.luc@avanade.com       Avanade Netherlands
 *              Mark Beerens   mark.beerens@avanade.com    Avanade Netherlands
 * 
 * Content:     This class stores the data and gets the data from the database
 *                 
 * */

#region [Using]
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.OleDb;
using Microsoft.DataWarehouse.Interfaces;
//using System.Diagnostics;
using System.Drawing.Drawing2D;
#endregion

namespace Avanade.Datamining.SVMPluginViewer
{
    public partial class SVMViewerUserControl : UserControl, IMiningModelViewerControl, IMiningModelViewerControl2
    {
        #region [Members]

        private String version = "2008";
        //Variables needed for querying the database
        public IDbConnection DbConnection { get; set; }
        private IServiceProvider serviceProvider = null;
        public IServiceProvider ServiceProvider
        {
            get { return this.serviceProvider; }
            set
            {
                if (this.serviceProvider != value)
                    this.serviceProvider = value;
            }
        }

        private OleDbCommand command;
        private OleDbConnection connection;
        private OleDbDataReader dataReader;

        private string miningModelName = string.Empty;
        public string MiningModelName
        {
            get { return this.miningModelName; }
            set
            {
                if (this.miningModelName != value)
                    this.miningModelName = value;
            }
        }

        private String _myConString = String.Empty;
        public string ConnectionString
        {
            get
            {
                if (this._myConString.Equals(String.Empty) && !DbConnection.ConnectionString.Equals(String.Empty))
                {
                    this._myConString = DbConnection.ConnectionString;
                }
                return _myConString;
            }
            set
            {
                if (this._myConString != value)
                {
                    this._myConString = value;
                }
            }
        }

        //the data cache for drawing the data
        private DataTable trainingset; //trianing
        private DataTable testingSet; //testing

        //the predict name
        private string predict_name;
        public String PredictName
        {
            get
            {
                if (predict_name == null || predict_name == String.Empty)
                {
                    predict_name = this.getPredictAttribute();
                }
                return predict_name;
            }
        }

        //a collection of the input attributes with their type [continous, discrete]
        private SortedDictionary<String, String> inputAttributes = new SortedDictionary<string, string>();
        //the max and min values of the continous attributes
        SortedDictionary<String, double> maxValues = new SortedDictionary<string, double>();
        SortedDictionary<String, double> minValues = new SortedDictionary<string, double>();
        //the discrete values of the discrete attributes
        SortedDictionary<String, LinkedList<String>> discreteValues = new SortedDictionary<string, LinkedList<string>>();

        //the predict values
        private string[] classes;

        //variables needed for drawing continuous data
        private double maxX, minX, maxY, minY;

        public double MaxX
        {
            get { return this.maxX; }
        }
        public double MinX
        {
            get { return this.minX; }
        }
        public double MaxY
        {
            get { return this.maxY; }
        }
        public double MinY
        {
            get { return this.minY; }
        }

        //the display name of the viewer
        static public string DisplayName
        {
            get { return "Avanade_Support_Vector_Machine"; }
        }

        //some values needed for displaying the accuracy
        private int numberOfTrainingSetExamples;
        private int numberOfTestSetExamples;
        private int correctlyClassifiedTrainingSetExamples;
        private int correctlyClassifiedTestSetExamples;
        #endregion

        #region [Querying for data]

        /// <summary>
        /// The method asks the algorithm for all the attribtues
        /// and stores it in the inputAttributes object
        /// </summary>
        private void getAttributes()
        {

            connection = new OleDbConnection(ConnectionString);
            connection.Open();

            // Check the status of our connection.
            if (dataReader != null)
            {
                if (!dataReader.IsClosed)
                    dataReader.Close();
            }

            // Create the OleDbCommand.
            string commandText = string.Format("SELECT GetAttributes() FROM [{0}]", this.MiningModelName);
            if (command == null)
            {
                command = new OleDbCommand();
            }
            command.Connection = connection;
            command.CommandText = commandText;

            // Execute the command
            dataReader = command.ExecuteReader();

            // Add code to extract information from the schema here.
            if (dataReader.Read())
            {
                OleDbDataReader reader = dataReader.GetData(0);
                while (reader.Read())
                {
                    inputAttributes.Add(reader.GetValue(0).ToString(), reader.GetValue(1).ToString());
                }
                reader.Close();
            }
            dataReader.Close();
            // Close the connection
            connection.Close();
        }

        /// <summary>
        /// This method asks for the predict attribute
        /// </summary>
        /// <returns></returns>
        private string getPredictAttribute()
        {
            string predictAttribute = "";

            connection = new OleDbConnection(this.ConnectionString);
            connection.Open();

            // Check the status of our connection.
            if (dataReader != null)
            {
                if (!dataReader.IsClosed)
                    dataReader.Close();
            }

            // Create the OleDbCommand.
            string commandText = string.Format("SELECT GetPredictAttribute() FROM [{0}]", this.MiningModelName);
            if (command == null)
            {
                command = new OleDbCommand();
            }
            command.Connection = connection;
            command.CommandText = commandText;


            // Execute the command
            dataReader = command.ExecuteReader();

            // Add code to extract information from the schema here.
            ///comboBox1.Items.Clear();
            if (dataReader.Read())
            {
                OleDbDataReader reader = dataReader.GetData(0);
                if (reader.Read())
                {
                    predictAttribute = reader.GetValue(0).ToString();
                }
                reader.Close();
            }
            dataReader.Close();
            // Close the connection
            connection.Close();
            return predictAttribute;
        }

        /// <summary>
        /// This method loads the predict values
        /// </summary>
        /// <returns></returns>
        public string[] getClasses()
        {

            if (classes == null)
            {
                LinkedList<string> myAttributes = new LinkedList<string>();
                connection = new OleDbConnection(this.ConnectionString);
                connection.Open();

                // Check the status of our connection.
                if (dataReader != null)
                {
                    if (!dataReader.IsClosed)
                        dataReader.Close();
                }

                // Create the OleDbCommand.
                string commandText = string.Format("SELECT GetClasses() FROM [{0}]", this.MiningModelName);
                if (command == null)
                {
                    command = new OleDbCommand();
                }
                command.Connection = connection;
                command.CommandText = commandText;


                // Execute the command
                dataReader = command.ExecuteReader();

                // Add code to extract information from the schema here.
                ///comboBox1.Items.Clear();
                if (dataReader.Read())
                {
                    OleDbDataReader reader = dataReader.GetData(0);
                    while (reader.Read())
                    {
                        myAttributes.AddLast(reader.GetValue(0).ToString());
                    }
                    reader.Close();
                }
                dataReader.Close();
                // Close the connection
                connection.Close();
                classes = new string[myAttributes.Count];
                myAttributes.CopyTo(classes, 0);
            }
            return classes;
        }

        /// <summary>
        /// This method loads the min and max values of the continuous attributes
        /// </summary>
        public void getMinMaxValues()
        {


            connection = new OleDbConnection(this.ConnectionString);
            connection.Open();

            // Check the status of our connection.
            if (dataReader != null)
            {
                if (!dataReader.IsClosed)
                    dataReader.Close();
            }

            // Create the OleDbCommand.
            string commandText = string.Format("SELECT GetMaxMinValues() FROM [{0}]", this.MiningModelName);
            if (command == null)
            {
                command = new OleDbCommand();
            }
            command.Connection = connection;
            command.CommandText = commandText;


            // Execute the command
            dataReader = command.ExecuteReader();

            // Add code to extract information from the schema here.
            ///comboBox1.Items.Clear();
            if (dataReader.Read())
            {
                OleDbDataReader reader = dataReader.GetData(0);
                while (reader.Read())
                {
                    maxValues.Add(reader.GetValue(0).ToString(), Convert.ToDouble(reader.GetValue(2).ToString()));
                    minValues.Add(reader.GetValue(0).ToString(), Convert.ToDouble(reader.GetValue(1).ToString()));
                }
                reader.Close();
            }
            dataReader.Close();
            // Close the connection
            connection.Close();
        }

        /// <summary>
        /// THis method gets the data for the visualization
        /// In contract to what the name suggests.. it also gets the testing data
        /// </summary>
        public void GetTrainingData()
        {
            bool bExceptionOccured = false;
            if ((data.Equals("Trainingdata") && trainingset == null) || (data.Equals("Testingdata") && testingSet == null && version == "2008"))
            {
                int numberOfExamples = 0;
                int numberOfCorrectExamples = 0;
                DataTable table = new DataTable();
                table = new DataTable();
                table.Columns.Add(new DataColumn("__predict"));          // predict values. The two underscores characters are used to prevent potential name conlficts with regard to existing name definitions in the trainingset.    
                table.Columns.Add(new DataColumn("__class"));            // class values

                foreach (KeyValuePair<String, String> input in inputAttributes)
                {
                    table.Columns.Add(new DataColumn(input.Key));
                    if (input.Value == "Continuous")
                    {
                        table.Columns.Add(new DataColumn(input.Key + "__scaled"));
                    }
                }

                connection = new OleDbConnection(this.ConnectionString);
                //Debug.WriteLine(this.ConnectionString);
                connection.Open();

                // Check the status of our connection.
                if (dataReader != null)
                {
                    if (!dataReader.IsClosed)
                        dataReader.Close();
                }

                // Create the OleDbCommand.
                // first, get the maximum and minumum values for each input column
                string commandText = string.Empty;
                // commandText = string.Format("SELECT [{0}] AS __PredictValues, x.[{0}] AS __ClassTrainingData, x.* FROM [{1}] NATURAL PREDICTION JOIN (SELECT * FROM [{1}].CASES) As x", predict_name, this.MiningModelName);
                string whereClause = "";
                if (version == "2008")
                {
                    if (data.Equals("Trainingdata"))
                    {
                        whereClause = " WHERE IsTrainingCase()";
                    }
                    else
                    {
                        whereClause = " WHERE IsTestCase()";
                    }
                }

                commandText = string.Format("SELECT [{0}] AS __PredictValues, x.[{0}] AS __ClassTrainingData, x.* FROM [{1}] NATURAL PREDICTION JOIN (SELECT * FROM [{1}].CASES {2}) As x", this.predict_name, this.MiningModelName, whereClause);
                // Comments: 1. The predict values are stored in the column with the column name "PredictValues" 
                //           2. The predict trainings values are stored in the column with the column name "ClassTrainingData".      
                //           3. All the other values are stored in columns with Names correspondig to the dataset in the database. 
                //Debug.WriteLine(commandText);
                if (command == null)
                {
                    command = new OleDbCommand();
                    command.CommandTimeout = 600;
                }
                command.Connection = connection;
                command.CommandText = commandText;
                DataTable table2 = new DataTable();
                OleDbDataAdapter da;
                // Execute the command
                try
                {
                    da = new OleDbDataAdapter(commandText, connection);
                    da.SelectCommand.CommandTimeout = 600;

                    da.Fill(table2);
                    //dataReader = new OleDbDataReader(table2);
                    //dataReader = 
                    
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    bExceptionOccured = true;
                }

                if (bExceptionOccured == false)        // Exception occured in command.ExecuteReader() ?
                {


                    // Decalare data row object
                    DataRow row;

                    // Initialize flag
                    // bool firstrecord = true;

                    int numberOfColumns = table2.Columns.Count;

                    SortedDictionary<String, double> maxContinuousValues = new SortedDictionary<string, double>();
                    SortedDictionary<String, double> minContinuousValues = new SortedDictionary<string, double>();
                    
                    foreach (DataRow row2 in table2.Rows)//dataReader.Read())
                    {

                        // create new row
                        row = table.NewRow();
                        numberOfExamples++;

                        //loop through the input values
                        foreach (KeyValuePair<String, String> pair in inputAttributes)
                        {
                            //take care of the discrete input atts
                            if (pair.Value == "Discrete")
                            {
                                String tmpValue = row2[pair.Key].ToString();
                                row[pair.Key] = tmpValue;

                                //update the datastructure containing all discrete values... 
                                //this makes the painting easy
                                LinkedList<String> tmpValues;
                                if (!discreteValues.ContainsKey(pair.Key))
                                {
                                    tmpValues = new LinkedList<String>();
                                }
                                else
                                {
                                    discreteValues.TryGetValue(pair.Key, out tmpValues);
                                    discreteValues.Remove(pair.Key);
                                }
                                if (!tmpValues.Contains(tmpValue))
                                {
                                    tmpValues.AddLast(tmpValue);
                                }
                                discreteValues.Add(pair.Key, tmpValues);
                            }
                            else //and of the continous input atts
                            {
                                double value = Convert.ToDouble(row2[pair.Key].ToString());

                                double minValue = 0;
                                double maxValue = 0;
                                minValues.TryGetValue(pair.Key, out minValue);
                                maxValues.TryGetValue(pair.Key, out maxValue);

                                //if the value is smaller than the stored min value, update
                                //this can happen, because min and max values of the training data values
                                if (value < minValue)
                                {
                                    minValues.Remove(pair.Key);
                                    minValues.Add(pair.Key, value);
                                    minValue = value;
                                }//do the same for maxvalue
                                else if (value > maxValue)
                                {
                                    maxValues.Remove(pair.Key);
                                    maxValues.Add(pair.Key, value);
                                    maxValue = value;
                                }

                                double scaled = ScaleValue(value, minValue, maxValue);
                                row[pair.Key] = value;
                                row[pair.Key + "__scaled"] = scaled;
                                //Debug.WriteLine("row: " + value + " scaled: " + scaled + " min: " + minValue + "max: " + maxValue);

                            }
                        }
                        row["__predict"] = row2["__PredictValues"].ToString();
                        row["__class"] = row2["__ClassTrainingData"].ToString();
                        if (row["__predict"].ToString() == row["__class"].ToString()){
                            numberOfCorrectExamples++;
                        }
                        table.Rows.Add(row);

                    }

                    // Close the connection and the reader
                    //da.Dispose();
                    connection.Close();
                }
                if (data.Equals("Trainingdata"))
                {
                    this.trainingset = table;
                    this.numberOfTrainingSetExamples = numberOfExamples;
                    this.correctlyClassifiedTrainingSetExamples = numberOfCorrectExamples;
                }
                else
                {
                    this.testingSet = table;
                    this.numberOfTestSetExamples = numberOfExamples;
                    this.correctlyClassifiedTestSetExamples = numberOfCorrectExamples;
                }
            }
        }

        /// <summary>
        /// This method scales continous input data to [0..1]
        /// </summary>
        /// <param name="input"></param>
        /// <param name="minimal"></param>
        /// <param name="maximal"></param>
        /// <returns></returns>
        private double ScaleValue(double input, double minimal, double maximal)
        {
            return (input - minimal) / (maximal - minimal);
        }

        /// <summary>
        /// returns the trianing accuracy
        /// </summary>
        /// <returns></returns>
        private String getTrainingAccuracy()
        {
            if (numberOfTrainingSetExamples == 0)
            {
                return "Unknown";
            }
            return ((double)correctlyClassifiedTrainingSetExamples / (double)numberOfTrainingSetExamples).ToString();
        }

        /// <summary>
        /// returns the testing accuracy
        /// </summary>
        /// <returns></returns>
        private String getTestAccuracy()
        {
            if (numberOfTestSetExamples == 0)
            {
                return "Unknown";
            }
            return ((double)correctlyClassifiedTestSetExamples / (double)numberOfTestSetExamples).ToString();
        }
        #endregion

        private void InitializeComponent()
        {
            this.SuspendLayout();
            // 
            // SVMViewerUserControl
            // 
            this.Name = "SVMViewerUserControl";
            this.Load += new System.EventHandler(this.SVMViewerUserControl_Load);
            this.ResumeLayout(false);

        }

        private void SVMViewerUserControl_Load(object sender, EventArgs e)
        {

        }
    }
}
